insert overwrite table jms_dm.dm_lmdm_three_segment_monitoring_dt partition (dt)
select
 t.deliver_name
,t.deliver_code
,t.dt as sign_scantime
,t.deliver_agent_name
,t.deliver_agent_code
,t1.provider_id
,t1.provider_desc
,t1.city_id
,t1.city_desc
,count(1) as all_total_num  --签收总票数
,count(1)- sum( t.first_code_fail ) as all_analysis_num  --解析票数（签收总票数-解析失败票数）
,count(1)- sum( t.first_code_fail ) -sum(first_code_error) as all_analysis_true_num  --解析票数（解析票数-一解析错误票数）

,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) as jms_total_num --D09--拼多多 D801--菜鸟
,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) -
    sum( case when order_source_code not in ('D09','D801','D67') then t.first_code_fail else 0 end  ) as jms_analysis_num
,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) -
    sum( case when order_source_code not in ('D09','D801','D67') then t.first_code_fail else 0 end  ) -
      sum( case when order_source_code not in ('D09','D801','D67') then t.first_code_error else 0 end  )  as jms_analysis_true_num

,sum( case when order_source_code ='D09' then 1 else 0 end  ) as thd_total_num --D09--拼多多 D801--菜鸟
,sum( case when order_source_code ='D09' then 1 else 0 end  ) -
    sum( case when order_source_code ='D09' then t.first_code_fail else 0 end  ) as thd_analysis_num
,sum( case when order_source_code ='D09' then 1 else 0 end  ) -
    sum( case when order_source_code ='D09' then t.first_code_fail else 0 end  ) -
      sum( case when order_source_code ='D09' then t.first_code_error else 0 end  )  as thd_analysis_true_num

,null as thd_own_num
,null as thd_own_num_passive

,sum( case when order_source_code ='D801' then 1 else 0 end  ) as qxt_total_num
,sum( case when order_source_code ='D801' then 1 else 0 end  ) -
    sum( case when order_source_code ='D801' then t.first_code_fail else 0 end  ) as qxt_analysis_num
,sum( case when order_source_code ='D801' then 1 else 0 end  ) -
    sum( case when order_source_code ='D801' then t.first_code_fail else 0 end  ) -
      sum( case when order_source_code ='D801' then t.first_code_error else 0 end  )  as qxt_analysis_true_num

,null as qxt_own_num
,null as qxt_own_num_passive

,1 as segment_type
,current_timestamp as etl_time
--D67紫金山
,sum( case when order_source_code ='D67' then 1 else 0 end  ) as zjs_total_num
,sum( case when order_source_code ='D67' then 1 else 0 end  ) -
    sum( case when order_source_code ='D67' then t.first_code_fail else 0 end  ) as zjs_analysis_num
,sum( case when order_source_code ='D67' then 1 else 0 end  ) -
    sum( case when order_source_code ='D67' then t.first_code_fail else 0 end  ) -
      sum( case when order_source_code ='D67' then t.first_code_error else 0 end  )  as zjs_analysis_true_num

,t.dt
from jms_dm.dm_lmdm_three_segment_monitoring_detail_dt t
join  jms_dim.dim_network_whole_massage t1 on t.deliver_code=t1.code

where t.dt = '{{ execution_date | cst_ds }}'
group by
 t.deliver_agent_name
,t.deliver_agent_code
,t.deliver_name
,t.deliver_code
,t1.provider_id
,t1.provider_desc
,t1.city_id
,t1.city_desc
,t.dt

union all
select
 t.deliver_name
,t.deliver_code
,t.dt as sign_scantime
,t.deliver_agent_name
,t.deliver_agent_code
,t1.provider_id
,t1.provider_desc
,t1.city_id
,t1.city_desc
,count(1) as all_total_num  --签收总票数
,count(1)- sum( t.second_code_fail ) as all_analysis_num  --解析票数（签收总票数-解析失败票数）
,count(1)- sum( t.second_code_fail ) -sum(second_code_error) as all_analysis_true_num  --解析票数（解析票数-一解析错误票数）

,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) as jms_total_num --D09--拼多多 D801--菜鸟
,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) -
    sum( case when order_source_code not in ('D09','D801','D67') then t.second_code_fail else 0 end  ) as jms_analysis_num
,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) -
    sum( case when order_source_code not in ('D09','D801','D67') then t.second_code_fail else 0 end  ) -
      sum( case when order_source_code not in ('D09','D801','D67') then t.second_code_error else 0 end  )  as jms_analysis_true_num

,sum( case when order_source_code ='D09' then 1 else 0 end  ) as thd_total_num --D09--拼多多 D801--菜鸟
,sum( case when order_source_code ='D09' then 1 else 0 end  ) -
    sum( case when order_source_code ='D09' then t.second_code_fail else 0 end  ) as thd_analysis_num
,sum( case when order_source_code ='D09' then 1 else 0 end  ) -
    sum( case when order_source_code ='D09' then t.second_code_fail else 0 end  ) -
      sum( case when order_source_code ='D09' then t.second_code_error else 0 end  )  as thd_analysis_true_num
,null as thd_own_num
,null as thd_own_num_passive

,sum( case when order_source_code ='D801' then 1 else 0 end  ) as qxt_total_num
,sum( case when order_source_code ='D801' then 1 else 0 end  ) -
    sum( case when order_source_code ='D801' then t.second_code_fail else 0 end  ) as qxt_analysis_num
,sum( case when order_source_code ='D801' then 1 else 0 end  ) -
    sum( case when order_source_code ='D801' then t.second_code_fail else 0 end  ) -
      sum( case when order_source_code ='D801' then t.second_code_error else 0 end  )  as qxt_analysis_true_num
,null as qxt_own_num
,null as qxt_own_num_passive
,2 as segment_type
,current_timestamp as etl_time
--D67紫金山
,sum( case when order_source_code ='D67' then 1 else 0 end  ) as zjs_total_num
,sum( case when order_source_code ='D67' then 1 else 0 end  ) -
    sum( case when order_source_code ='D67' then t.second_code_fail else 0 end  ) as zjs_analysis_num
,sum( case when order_source_code ='D67' then 1 else 0 end  ) -
    sum( case when order_source_code ='D67' then t.second_code_fail else 0 end  ) -
      sum( case when order_source_code ='D67' then t.second_code_error else 0 end  )  as zjs_analysis_true_num
,t.dt
from jms_dm.dm_lmdm_three_segment_monitoring_detail_dt t
join  jms_dim.dim_network_whole_massage t1 on t.deliver_code=t1.code
where t.dt = '{{ execution_date | cst_ds }}'
group by
 t.deliver_agent_name
,t.deliver_agent_code
,t.deliver_name
,t.deliver_code
,t1.provider_id
,t1.provider_desc
,t1.city_id
,t1.city_desc
,t.dt

union all
select
 t.deliver_name
,t.deliver_code
,t.dt as sign_scantime
,t.deliver_agent_name
,t.deliver_agent_code
,t1.provider_id
,t1.provider_desc
,t1.city_id
,t1.city_desc
,count(1) as all_total_num  --签收总票数
,count(1)- sum( t.third_code_fail ) as all_analysis_num  --解析票数（签收总票数-解析失败票数）
,count(1)- sum( t.third_code_fail ) -sum(third_code_error) as all_analysis_true_num  --解析票数（解析票数-一解析错误票数）

,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) as jms_total_num --D09--拼多多 D801--菜鸟
,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) -
    sum( case when order_source_code not in ('D09','D801','D67') then t.third_code_fail else 0 end  ) as jms_analysis_num
,sum( case when order_source_code not in ('D09','D801','D67') then 1 else 0 end  ) -
    sum( case when order_source_code not in ('D09','D801','D67') then t.third_code_fail else 0 end  ) -
      sum( case when order_source_code not in ('D09','D801','D67') then t.third_code_error else 0 end  )  as jms_analysis_true_num

,sum( case when order_source_code ='D09' then 1 else 0 end  ) as thd_total_num --D09--拼多多 D801--菜鸟
,sum( case when order_source_code ='D09' then 1 else 0 end  ) -
    sum( case when order_source_code ='D09' then t.third_code_fail else 0 end  ) as thd_analysis_num
,sum( case when order_source_code ='D09' then 1 else 0 end  ) -
    sum( case when order_source_code ='D09' then t.third_code_fail else 0 end  ) -
      sum( case when order_source_code ='D09' then t.third_code_error else 0 end  )  as thd_analysis_true_num
,null as thd_own_num
,null as thd_own_num_passive

,sum( case when order_source_code ='D801' then 1 else 0 end  ) as qxt_total_num
,sum( case when order_source_code ='D801' then 1 else 0 end  ) -
    sum( case when order_source_code ='D801' then t.third_code_fail else 0 end  ) as qxt_analysis_num
,sum( case when order_source_code ='D801' then 1 else 0 end  ) -
    sum( case when order_source_code ='D801' then t.third_code_fail else 0 end  ) -
      sum( case when order_source_code ='D801' then t.third_code_error else 0 end  )  as qxt_analysis_true_num
,null as qxt_own_num
,null as qxt_own_num_passive
,3 as segment_type
,current_timestamp as etl_time
--D67紫金山
,sum( case when order_source_code ='D67' then 1 else 0 end  ) as zjs_total_num
,sum( case when order_source_code ='D67' then 1 else 0 end  ) -
    sum( case when order_source_code ='D67' then t.third_code_fail else 0 end  ) as zjs_analysis_num
,sum( case when order_source_code ='D67' then 1 else 0 end  ) -
    sum( case when order_source_code ='D67' then t.third_code_fail else 0 end  ) -
      sum( case when order_source_code ='D67' then t.third_code_error else 0 end  )  as zjs_analysis_true_num
,t.dt

from jms_dm.dm_lmdm_three_segment_monitoring_detail_dt t
join  jms_dim.dim_network_whole_massage t1 on t.deliver_code=t1.code
where t.dt = '{{ execution_date | cst_ds }}'
group by
 t.deliver_agent_name
,t.deliver_agent_code
,t.deliver_name
,t.deliver_code
,t1.provider_id
,t1.provider_desc
,t1.city_id
,t1.city_desc
,t.dt
distribute by dt, abs(hash(deliver_code)) % 3
;